﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using TT.HelpInfo;
using Bill.BasDLL.ety;
using SQLHelper;
using System.Data;

namespace Bill.BasDLL.dao
{
    public class daoChrgFavour : basClassDeclare
    {
        public daoChrgFavour(TT.Module.sysLogin.etyBaeSystemParamter basSysPara)
        {
            _sqlHelp = basSysPara.SqlHelp;
            sPortCode = basSysPara.PORT_CODE;
            sUserID = basSysPara.UserID;
        }

        public bool Insert(etyChrgFavour eyFav)
        {
            string sql = "insert into CHRG_FAVOUR (PORT_CODE,CUSTOMER,FAV_ID,ITEM,FAV_WGT_FROM,FAV_WGT_TO,SUM_WGT_FROM,SUM_WGT_TO,SUM_MODE,CHRG_FLT1,CHRG_FLT2,CHRG_FLT3,CHRG_FLT4,CHRG_FLT5,LST_USER,LST_UPD_DATE) "
                + "values(:PORT_CODE,:CUSTOMER,(select nvl(max(FAV_ID)+1,1) from CHRG_FAVOUR),:ITEM,:FAV_WGT_FROM,:FAV_WGT_TO,:SUM_WGT_FROM,:SUM_WGT_TO,:SUM_MODE,:BUSS_TYPE,:AREA,:SF,:CITY,:QX,:LST_USER,sysdate)";

            return _sqlHelp.ExecuteTrans(sql, eyFav).Exec_Success;

            //IList<etyChrgFavour> ilFav = new List<etyChrgFavour>();
            //ilFav.Add(eyFav);
            //_sqlHelp.BeginTrans();
            //daoResult rst = _sqlHelp.Execute<etyChrgFavour>(sql, ilFav);
            //if (!rst.Exec_Success)
            //{
            //    _sqlHelp.Rollback();
            //    _sqlHelp.EndTrans();
            //    return false;
            //}
            //_sqlHelp.Commit();
            //_sqlHelp.EndTrans();
            //return true;
        }

        public bool Update(etyChrgFavour eyFav)
        {
            string sql = "update CHRG_FAVOUR set CUSTOMER=:CUSTOMER,ITEM=:ITEM,FAV_WGT_FROM=:FAV_WGT_FROM,FAV_WGT_TO=:FAV_WGT_TO,SUM_WGT_FROM=:SUM_WGT_FROM,SUM_WGT_TO=:SUM_WGT_TO,SUM_MODE=:SUM_MODE,LST_USER=:LST_USER,lst_upd_date=sysdate,CHRG_FLT1=:BUSS_TYPE,CHRG_FLT2=:AREA,CHRG_FLT3=:SF,CHRG_FLT4=:CITY,CHRG_FLT5=:QX "
                 + " where port_code=:PORT_CODE and FAV_ID=:FAV_ID ";

            return _sqlHelp.ExecuteTrans(sql, eyFav).Exec_Success;

            //IList<etyChrgFavour> ilFav = new List<etyChrgFavour>();
            //ilFav.Add(eyFav);
            //_sqlHelp.BeginTrans();
            //daoResult rst = _sqlHelp.Execute<etyChrgFavour>(sql, ilFav);
            //if (!rst.Exec_Success)
            //{
            //    _sqlHelp.Rollback();
            //    _sqlHelp.EndTrans();
            //    return false;
            //}
            //_sqlHelp.Commit();
            //_sqlHelp.EndTrans();
            //return true;
        }

        public bool Delete(etyChrgFavour eyFav)
        {
            if (string.IsNullOrWhiteSpace(eyFav.FAV_ID))
                return false;
            _sqlHelp.BeginTrans();
            //先删除对应的价格表
            string sql = "delete BILL_price where FAV_SEQ_ID in (select price_id from chrg_favour_price where fav_id=:FAV_ID) ";
            daoResult rst = _sqlHelp.ExecuteSQL(sql, new string[] { eyFav.FAV_ID });
            if (!rst.Exec_Success)
            {
                _sqlHelp.Rollback();
                _sqlHelp.EndTrans();
                return false;
            }
            //再删除达量明细表
            sql = "delete chrg_favour_price where FAV_ID=:FAV_ID ";
            rst = _sqlHelp.ExecuteSQL(sql, new string[] { eyFav.FAV_ID });
            if (!rst.Exec_Success)
            {
                _sqlHelp.Rollback();
                _sqlHelp.EndTrans();
                return false;
            }
            //最后删除达量表
            sql = "delete from CHRG_FAVOUR "
                + " where FAV_ID=:FAV_ID ";

            rst = _sqlHelp.ExecuteSQL(sql, new string[] { eyFav.FAV_ID });
            if (!rst.Exec_Success)
            {
                _sqlHelp.Rollback();
                _sqlHelp.EndTrans();
                return false;
            }
            _sqlHelp.Commit();
            _sqlHelp.EndTrans();
            return true;
        }

        public DataTable GetChrgFavour()
        {
            string sql = string.Empty;
            sql = "select f.CUSTOMER,c.CLIENT_NAME,f.ITEM,BC.ITEM_DESC,"
                + "f.CHRG_FLT1 AS BUSS_TYPE,BT.CODE_DESC AS BUSS_TYPE_DESC,f.CHRG_FLT2 AS AREA,"
                + "f.CHRG_FLT3 AS SF,SF.X_CNAME AS SF_DESC,f.CHRG_FLT4 AS CITY,CT.X_CNAME AS CITY_DESC,F.CHRG_FLT5 AS QX,QX.X_CNAME AS QX_DESC,"
                +"f.FAV_WGT_FROM,f.FAV_WGT_TO,f.SUM_WGT_FROM,f.SUM_WGT_TO,f.SUM_MODE,F.FAV_ID "
                + " from CHRG_FAVOUR f left join bas_client c on f.customer=to_char(c.CLIENT_ID) "
                + " left join code_ref BT on f.CHRG_FLT1=BT.code and BT.item='BUSS_TYPE' "
                + " left join bill_code BC on F.port_code=bc.port_code and f.item=Bc.item "
                + " LEFT JOIN B_SF SF ON F.CHRG_FLT3=TO_CHAR(SF.X_CODE) "
                +" LEFT JOIN B_CITY CT ON F.CHRG_FLT4=TO_CHAR(CT.X_CODE) "
                +" LEFT JOIN B_QX QX ON F.CHRG_FLT5=TO_CHAR(QX.X_CODE) "
                +" where F.port_code=:PORT_CODE order by F.CUSTOMER,F.item,F.FAV_ID ";
            DataTable dt;
            dt = _sqlHelp.ExecuteReader(sql, new string[] { sPortCode });
            return dt;
        }
        public DataTable GetClient()
        {
            string sql = string.Empty;
            sql = "select CLIENT_ID,CLIENT_NAME from bas_client " 
                + " order by CLIENT_ID ";
            DataTable dt;
            dt = _sqlHelp.ExecuteReader(sql, new string[] { });
            return dt;
        }

        public DataTable GetBillCode()
        {
            string sql = string.Empty;
            sql = "select ITEM,ITEM_DESC,ITEM_UNIT FROM BILL_CODE WHERE PORT_CODE=:PORT_CODE ORDER BY ITEM ";
            DataTable dt;
            dt = _sqlHelp.ExecuteReader(sql, new string[] { sPortCode });
            return dt;
        }

        public DataTable GetFavPrice(string favId)
        {
            string sql = string.Empty;
            sql = "select PRICE_ID,METHOD,QTY,SIGN,UNIT from CHRG_FAVOUR_PRICE where fav_id=:FAV_ID ORDER BY METHOD,QTY ";
            DataTable dt;
            dt = _sqlHelp.ExecuteReader(sql, new string[] { favId });
            return dt;
        }

        public bool AddFavPrice(etyFavourPrice eyFavPic)
        {
            string sql = "insert into CHRG_FAVOUR_PRICE (PRICE_ID,FAV_ID,METHOD,QTY,SIGN,UNIT,LST_USER,LST_UPD_DATE) "
                + "values((select nvl(max(PRICE_ID)+1,1) from CHRG_FAVOUR_PRICE),:FAV_ID,:METHOD,:QTY,:SIGN,:UNIT,:LST_USER,sysdate)";

            IList<etyFavourPrice> ilFavPic = new List<etyFavourPrice>();
            ilFavPic.Add(eyFavPic);
            _sqlHelp.BeginTrans();
            daoResult rst = _sqlHelp.Execute<etyFavourPrice>(sql, ilFavPic);
            if (!rst.Exec_Success)
            {
                _sqlHelp.Rollback();
                _sqlHelp.EndTrans();
                return false;
            }
            _sqlHelp.Commit();
            _sqlHelp.EndTrans();
            return true;
        }

        public bool DelFavPrice(etyFavourPrice eyFavPic)
        {
            string sql = "delete from CHRG_FAVOUR_PRICE "
                + " where PRICE_ID=:PRICE_ID ";

            return _sqlHelp.ExecuteTrans(sql, eyFavPic).Exec_Success;

            //IList<etyFavourPrice> ilList = new List<etyFavourPrice>();
            //ilList.Add(eyFavPic);
            //_sqlHelp.BeginTrans();
            //daoResult rst = _sqlHelp.Execute<etyFavourPrice>(sql, ilList);
            //if (!rst.Exec_Success)
            //{
            //    _sqlHelp.Rollback();
            //    _sqlHelp.EndTrans();
            //    return false;
            //}
            //_sqlHelp.Commit();
            //_sqlHelp.EndTrans();
            //return true;
        }
        public DataTable GetArea(string clientId, string bussType)
        {
            string sql = string.Empty;
            sql = "select AREA_NAME FROM bas_area WHERE PORT_CODE=:PORT_CODE AND CLIENT_ID=:CLIENT_ID AND BUSS_TYPE=:BUSS_TYPE group by AREA_NAME ORDER BY AREA_NAME ";
            DataTable dt;
            dt = _sqlHelp.ExecuteReader(sql, new string[] { sPortCode, clientId, bussType });
            return dt;
        }
    }
}
